package com.aaa.book_mall.dao.impl;

import com.aaa.book_mall.dao.RequestDao;
import com.aaa.book_mall.entity.Request;
import com.aaa.book_mall.util.JDBCUtil;

import java.util.List;
import java.util.Map;

/**
 * fileName:RequestDaoImpl
 * description:
 * author:付亚星
 * createTime:2021/1/18 11:53
 * version:1.0.0
 */
public class RequestDaoImpl implements RequestDao {
    /**
     * 查询表中得所有状态为1的数据
     * @param request
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllRequest(Request request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from t_request where status=1");
        //          根据条件拼接sql语句
        sql.append(" limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(),(request.getPage() - 1) * request.getLimit(), request.getLimit());
        return maps;
    }

    @Override
    public List<Map<String, Object>> queryNoAuditInfo(Request request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id,number,staffid,staffname,bookcount,bookprice,createdate,status from t_request where status='0'");
        sql.append(" limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(), (request.getPage() - 1) * request.getLimit(), request.getLimit());
        System.out.println("querynoauditinfo");
        System.out.println(maps);
        return maps;
    }

    /**
     * 获取所有记录，不论状态
     * @return
     */
    @Override
    public int queryCountAllInfo() {
        String sql="select count(*) from t_request";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    /**
     * 查询表中共有多少条数据,状态为0的
     * @return
     */
    @Override
    public int queryCountInfo() {
        String sql="select count(*) from t_request where status='0'";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    /**
     * 查询状态为1的数据条数
     * @return
     */
    @Override
    public int showByCountInfo() {
        String sql="select count(*) from t_request where status='1'";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    @Override
    public List<Map<String, Object>> showNoAuditInfo(Request request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from t_request where status='2'");
        //          根据条件拼接sql语句
        sql.append(" limit ?,?");
        // List<Map<String, Object>> list = JDBCUtil.queryListMap(sql, request.getId(), request.getNumber(), request.getStaffid(), request.getStaffname(), request.getBookcount(), request.getBookprice(), request.getCreatedate(), request.getStatus());
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(), (request.getPage() - 1) * request.getLimit(), request.getLimit());
        return maps;
    }

    /**
     * 查询被驳回的总条数
     * @return
     */
    @Override
    public int showCountInfo() {
        String sql="select count(*) from t_request where status='2'";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    /**
     * 删除ststus为2的数据记录
     * @param status
     * @return
     */
    @Override
    public int deleteRequest(String status) {
        String sql = "delete from t_request where status='2";
        int update = JDBCUtil.update(sql, status);
        System.out.println("删除被驳回记录成功");
        return update;
    }

    @Override
    public int getRequestCount(Request request) {
        return 0;
    }

    /**
     * 新增
     * @param request
     * @return
     */
    @Override
    public int insertRequest(Request request) {
        String sql = "insert into t_request values(null,?,?,?,?,?,?,?)";
        int i = JDBCUtil.update(sql, request.getNumber(),request.getStaffid(),request.getStaffname(),request.getBookcount(),request.getBookprice(),request.getCreatedate(),request.getStatus());
        return i;
    }

    /**
     * 修改订单状态
     * @param number
     * @param status
     * @return
     */
    @Override
    public int updateStatus(String number, String status) {
        String sql="update t_request set status=? where number=?";
        int i = JDBCUtil.update(sql,status, number);
        return i;
    }

    /**
     * 查询入库总金额
     * @return
     */
    @Override
    public List<Map<String, Object>> selectRequest() {
        String sql = "select tr.createdate,sum(tr.bookprice) from t_request tr where tr.`status`='1' GROUP BY tr.createdate ";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql);
        if(maps.size()>0){
            return maps;
        }
        return null;
    }

    /**
     * 查询入库数目
     * @return
     */
    @Override
    public int selectCount() {
        String sql = "select count(*) from (select count(*), tb.bookname,tr.bookprice from t_request tr INNER JOIN t_detail td ON tr.bookcount=td.bookcount INNER JOIN t_book tb ON tb.bookid=td.bookid GROUP BY tb.bookname) Count";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());

        }
        return count;
    }
}
